/*
 * Процесс для отчета "Анализ субконто"
 * Создан: Ибраев Е.А. 12.11.2012
 * 
 */
package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import net.sf.jasperreports.engine.JRException;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.io.filefilter.WildcardFileFilter;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachment;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MProcess;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class FI_SubcontoAnalyze extends SvrProcess {
	// Задаются колонки для запроса в getSQL
	private static int colAD_Client_ID = 1;
	private static int colAD_Org_ID = 2;
	private static int colAccount_ID = 3;
	private static int colValue = 4;
	private static int colSubconto_ID = 5;
	private static int colName = 6;
	private static int colC_ChargeType_ID =7;
	private static int colC_ChargeType_Name = 8;
	private static int colB_AmtAcctdr = 9;
	private static int colB_AmtAcctcr = 10;
	private static int colB_AmtSourcedr = 11;
	private static int colB_AmtSourcecr = 12;
	private static int colE_AmtAcctdr = 13;
	private static int colE_AmtAcctcr = 14;
	private static int colE_AmtSourcedr = 15;
	private static int colE_AmtSourcecr = 16;
	private static int colAmtAcctdr = 17;
	private static int colAmtAcctcr = 18;
	private static int colAmtSourcedr = 19;
	private static int colAmtSourcecr = 20;
	
	private String getSql(BigDecimal pAD_Client_ID, BigDecimal pFI_SubcontoType_ID,Timestamp pBDP,Timestamp pEDP, BigDecimal pSubconto_ID) {
		if (pSubconto_ID == null) {
			pSubconto_ID = new BigDecimal(0);
		}
		Boolean C_Charge = isC_Charge(pFI_SubcontoType_ID);
		String strBDP = pBDP.toString().substring(0, 10);
		String strEDP = pEDP.toString().substring(0, 10);
		String result = "select coalesce(fsa1.ad_org_id,fsa2.ad_org_id) as ad_org_id \n" //colAD_Client_ID = 1
                           + ", coalesce(fsa1.ad_client_id,fsa2.ad_client_id) as ad_client_id \n"  //colAD_Org_ID = 2
                           + ", coalesce(fsa1.account_id,fsa2.account_id) as account_id \n"  //colAccount_ID = 3
                           + ", coalesce(fsa1.value,fsa2.value) as value \n"  //colValue = 4
                           + ", coalesce(fsa1.subconto_id, fsa2.subconto_id) as subconto_id \n" //colSubconto_ID = 5
                           + ", coalesce(fsa1.name,fsa2.name) as name \n" // colName = 6
                           + ", " + ( C_Charge ? "coalesce(cct1.C_ChargeType_ID,cct2.C_ChargeType_ID)" : "0" ) + " as C_ChargeType_ID \n" // colC_ChargeType_ID =7 
                           + ", " + ( C_Charge ? "coalesce(coalesce(cct1.FI_Code,'') || cct1.Name,coalesce(cct2.FI_Code,'') || cct2.Name)" : "null" ) + " as C_ChargeType_Name \n" // colC_ChargeType_Name = 8;
                           + ", fsa1.amtacctdr as b_amtacctdr \n" // colB_AmtAcctdr = 9;
                           + ", fsa1.amtacctcr as b_amtacctcr \n" // colB_AmtAcctcr = 10;
                           + ", fsa1.amtsourcedr as b_amtsourcedr \n" // colB_AmtSourcedr = 11;
                           + ", fsa1.amtsourcecr as b_amtsourcecr \n" // colB_AmtSourcecr = 12; 
                           + ", fsa2.amtacctdr as e_amtacctdr \n" // colE_AmtAcctdr = 13;
                           + ", fsa2.amtacctcr as e_amtacctcr \n" // colE_AmtAcctcr = 14;
                           + ", fsa2.amtsourcedr as e_amtsourcedr \n" // colE_AmtSourcedr = 15;
                           + ", fsa2.amtsourcecr as e_amtsourcecr \n" // colE_AmtSourcecr = 16;
                           + ", coalesce(fsa2.amtacctdr,0) - coalesce(fsa1.amtacctdr,0) as amtacctdr \n" // colAmtAcctdr = 17;
                           + ", coalesce(fsa2.amtacctcr,0) - coalesce(fsa1.amtacctcr,0) as amtacctcr \n" // colAmtAcctcr = 18;
                           + ", coalesce(fsa2.amtsourcedr,0) - coalesce(fsa1.amtsourcedr,0) as amtsourcedr \n" // colAmtSourcedr = 19;
                           + ", coalesce(fsa2.amtsourcecr,0) - coalesce(fsa1.amtsourcecr,0) as amtsourcecr \n" // colAmtSourcecr = 20; 
                      + "from adempiere.\"FI_subconto_analyze\"(" + pFI_SubcontoType_ID.toString()+ "::numeric,'1900-01-01','"+strBDP+"') fsa1 \n" 
                      + "full join adempiere.\"FI_subconto_analyze\"(" + pFI_SubcontoType_ID.toString()+ "::numeric,'1900-01-01','"+strEDP+"') fsa2 \n"
                             + "on fsa2.ad_client_id = fsa1.ad_client_id \n"
                            + "and fsa2.ad_org_id = fsa1.ad_org_id \n"
                            + "and fsa2.account_id = fsa1.account_id \n"
                            + "and fsa2.subconto_id = fsa1.subconto_id \n"
                      + ( C_Charge ? "left join adempiere.C_ChargeType cct1 \n"
                      + " on cct1.C_ChargeType_Id in (select cc1.C_ChargeType_Id from C_Charge cc1 where cc1.C_Charge_ID = fsa1.Subconto_ID) \n": "" )      
                      + ( C_Charge ? "left join adempiere.C_ChargeType cct2 \n"
                      + " on cct2.C_ChargeType_Id in (select cc2.C_ChargeType_Id from C_Charge cc2 where cc2.C_Charge_ID = fsa2.Subconto_ID) \n": "" )      
                      + "where coalesce(fsa1.ad_client_id,fsa2.ad_client_id) = " + pAD_Client_ID.toString()+ " \n"
                        + "and (coalesce(fsa1.subconto_id,fsa2.subconto_id) = " + pSubconto_ID.toString()+ " or " + pSubconto_ID.toString()+ " = 0) \n"
                        + "order by C_ChargeType_Name, name, value;";
		s_log.log(Level.INFO, result);
		return result;
	}

	private static CLogger s_log = CLogger.getCLogger(FI_SubcontoAnalyze.class);
	private Properties m_ctx;
	private ProcessInfo pi;
	private String path = "";
	private MAttachment attachment;
	
	protected BigDecimal AD_Client_ID = new BigDecimal(0);
	protected BigDecimal AD_Org_ID = new BigDecimal(0);
	protected Timestamp pBDP = null;
	protected Timestamp pEDP = null;
	protected BigDecimal FI_SubcontoType_ID = new BigDecimal(0);
	protected BigDecimal Subconto_ID = new BigDecimal(0);
	protected String WithoutSubaccount = "N";
	
	
	@Override
	protected void prepare() {
		// TODO Auto-generated method stub
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			log.info("Parameter: " + name);
			if (para[i].getParameter() == null);
			else if (name.equals("AD_Org_ID"))
				AD_Org_ID = (BigDecimal)para[i].getParameter();
			else if (name.equals("pBDP"))
				pBDP = (Timestamp)para[i].getParameter();
			else if (name.equals("pEDP"))
				pEDP = (Timestamp)para[i].getParameter();
			else if (name.equals("FI_SubcontoType_ID"))
				FI_SubcontoType_ID = (BigDecimal)para[i].getParameter();
			else if (name.equals("V_Subconto_ID"))
				Subconto_ID = (BigDecimal)para[i].getParameter();
			else if (name.equals("WithoutSubaccount"))
				WithoutSubaccount = (String)para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		path = Util.localFilePath;
		AD_Client_ID  = new BigDecimal(getAD_Client_ID());
	}

	@Override
	protected String doIt() throws Exception {
		// TODO Auto-generated method stub
		String result = null;
		result = MakeExcel();

		return result;
	}
	
	private String MakeExcel() throws Exception {
		int row = 0;
		int col = 0;
		Label label = null;
		Number number = null;
		Boolean C_Charge = isC_Charge(FI_SubcontoType_ID);
		
		String templateName = "FI_subcontoanalyze.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);

//		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(getXlsName("Temporary Subconto Analyze")); 
//		fullPath.append("Temporary Subconto Analyze");
//		fullPath.append(fileExtension);	
		
        File templateCopy = new File(fullPath.toString());
        File temporaryFile = downloadAttachment("attachment: " + templateName); // entry.getFile(Utils.localFilePath+templateName);

        Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
        } catch (Exception e) {
        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
        	return "Error tableBook.";
        }
		
		FI_SubcontoType[] lines = getFI_SubcontoType();

		WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
		WritableFont arial10ptNoBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
		WritableFont arial8ptNoBold = new WritableFont(WritableFont.ARIAL, 8, WritableFont.NO_BOLD);
		WritableFont arial8ptBold = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);
		
	       // Style for value column
	    WritableCellFormat valuestyle = new WritableCellFormat(arial8ptNoBold);
	    valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setAlignment(Alignment.LEFT);
	    valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    valuestyle.setWrap(true);
	    valuestyle.setBackground(Colour.WHITE);

	    // Style for value column
	    WritableCellFormat numericstyle = new WritableCellFormat(arial8ptNoBold,NumberFormats.THOUSANDS_FLOAT);
	    numericstyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	    numericstyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
	    numericstyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	    numericstyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	    numericstyle.setAlignment(Alignment.RIGHT);
	    numericstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    numericstyle.setWrap(true);
	    numericstyle.setBackground(Colour.WHITE);
	    
	      //Style for total row
	    WritableCellFormat totalrowstyle = new WritableCellFormat(arial8ptBold);
	    totalrowstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    totalrowstyle.setAlignment(Alignment.LEFT);
	    totalrowstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalrowstyle.setWrap(true);
	    totalrowstyle.setBackground(Colour.WHITE);

	    //Style for total row
	    WritableCellFormat totalnumstyle = new WritableCellFormat(arial8ptBold,NumberFormats.THOUSANDS_FLOAT);
	    totalnumstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    totalnumstyle.setAlignment(Alignment.RIGHT);
	    totalnumstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalnumstyle.setWrap(true);
	    totalnumstyle.setBackground(Colour.WHITE);

	    //Style for total row
	    WritableCellFormat totalnumstyleName = new WritableCellFormat(arial8ptBold,NumberFormats.THOUSANDS_FLOAT);
	    totalnumstyleName.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    totalnumstyleName.setAlignment(Alignment.LEFT);
	    totalnumstyleName.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalnumstyleName.setWrap(true);
	    totalnumstyleName.setBackground(Colour.WHITE);
	    
	      //Style for total row
	    WritableCellFormat totalrowstyle1 = new WritableCellFormat(arial10ptBold);
	    totalrowstyle1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    totalrowstyle1.setAlignment(Alignment.RIGHT);
	    totalrowstyle1.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalrowstyle1.setWrap(true);
	    totalrowstyle1.setBackground(Colour.WHITE);
	    
	      //Style for total row
	    WritableCellFormat totalrowstyle2 = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT);
	    totalrowstyle2.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    totalrowstyle2.setAlignment(Alignment.RIGHT);
	    totalrowstyle2.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalrowstyle2.setWrap(true);
	    totalrowstyle2.setBackground(Colour.WHITE);
	    
	      //Style for total C_ChargeType
	    WritableCellFormat C_ChargeTyperowstyle = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT);
	    C_ChargeTyperowstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    C_ChargeTyperowstyle.setAlignment(Alignment.RIGHT);
	    C_ChargeTyperowstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    C_ChargeTyperowstyle.setWrap(true);
	    C_ChargeTyperowstyle.setBackground(Colour.WHITE);
	    
	      //Style for total C_ChargeType
	    WritableCellFormat C_ChargeTyperowstyleName = new WritableCellFormat(arial10ptBold);
	    C_ChargeTyperowstyleName.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    C_ChargeTyperowstyleName.setAlignment(Alignment.LEFT);
	    C_ChargeTyperowstyleName.setVerticalAlignment(VerticalAlignment.CENTRE);
	    C_ChargeTyperowstyleName.setWrap(true);
	    C_ChargeTyperowstyleName.setBackground(Colour.WHITE);
	    
	      //Style for total row
	    WritableCellFormat defaultrowstyle = new WritableCellFormat(arial10ptBold);
//	    defaultrowstyle.setBackground(Colour.CORAL);
	      //Style for total row
	    WritableCellFormat partrowstyle = new WritableCellFormat(arial8ptBold);
	    partrowstyle.setBackground(Colour.getInternalColour(26));
	      //Style for total row
	    WritableCellFormat subcontostyle = new WritableCellFormat(arial10ptNoBold);
	    subcontostyle.setBackground(Colour.WHITE);
	    
	    subcontostyle.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
	    subcontostyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
	    subcontostyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	    subcontostyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);

	    ExcelCell cellPeriod = Util.getCellStart(tableBook,"period");
	    String period = "Период с " + pBDP.toString().substring(8, 10) + "." + pBDP.toString().substring(5, 7) + "." + pBDP.toString().substring(0, 4) 
	    		      + " по " + pEDP.toString().substring(8, 10) + "." + pEDP.toString().substring(5, 7) + "." + pEDP.toString().substring(0, 4); 
		label = new Label(cellPeriod.getC(), cellPeriod.getR(), period , defaultrowstyle);
		sheet.addCell(label);
	    
	    ExcelCell cellOrg = Util.getCellStart(tableBook,"ad_org");
		label = new Label(cellOrg.getC(), cellOrg.getR(), getOrgName() , defaultrowstyle);
		sheet.addCell(label);
		
		ExcelCell cellSubconto = Util.getCellStart(tableBook,"subconto");
		String subcontoType = getSubcontoTypeName();
		label = new Label(cellSubconto.getC(), cellSubconto.getR(), subcontoType , subcontostyle);
		sheet.addCell(label);
	    
	      // 
        ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
	    col = cellStart.getC(); 
	    row = cellStart.getR()-1;
	    double Sum_B_AmtAcctdr=0;
	    double Sum_B_AmtAcctcr=0;
	    double Sum_AmtAcctdr=0;
	    double Sum_AmtAcctcr=0;
	    double Sum_E_AmtAcctdr=0;
	    double Sum_E_AmtAcctcr=0;

	    double lineSum_B_AmtAcctdr=0;
	    double lineSum_B_AmtAcctcr=0;
	    double lineSum_AmtAcctdr=0;
	    double lineSum_AmtAcctcr=0;
	    double lineSum_E_AmtAcctdr=0;
	    double lineSum_E_AmtAcctcr=0;
	    
	    double C_ChargeType_lineSum_B_AmtAcctdr = 0;
	    double C_ChargeType_lineSum_B_AmtAcctcr = 0;
	    double C_ChargeType_lineSum_AmtAcctdr = 0;
	    double C_ChargeType_lineSum_AmtAcctcr = 0;
	    double C_ChargeType_lineSum_E_AmtAcctdr = 0;
	    double C_ChargeType_lineSum_E_AmtAcctcr = 0;
	    
	    int rowC_ChargeType = 0;
	    int rowSubconto_ID = 0;
	    int oldC_ChargeType_ID = 0;
	    
	    int lineSubconto_ID = 0;
	    String rowName = "";
		for(FI_SubcontoType line:lines) {
			
			if (lineSubconto_ID != line.getSubconto_ID()) {
/*				
				if (lineSubconto_ID != 0) {
					row ++;
					label = new Label(col, row, rowName, totalrowstyle);
					sheet.addCell(label);
					number = new Number(col+1, row, lineSum_B_AmtAcctdr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+2, row, lineSum_B_AmtAcctcr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+3, row, lineSum_AmtAcctdr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+4, row, lineSum_AmtAcctcr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+5, row, lineSum_E_AmtAcctdr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+6, row, lineSum_E_AmtAcctcr, totalnumstyle);
					sheet.addCell(number);
					
					lineSum_B_AmtAcctdr=0;
				    lineSum_B_AmtAcctcr=0;
				    lineSum_AmtAcctdr=0;
				    lineSum_AmtAcctcr=0;
				    lineSum_E_AmtAcctdr=0;
				    lineSum_E_AmtAcctcr=0;
				}
*/				

				if (C_Charge && line.getC_ChargeType_ID() != oldC_ChargeType_ID) {
					row ++;
					label = new Label(col, row, line.getC_ChargeType_Name(), C_ChargeTyperowstyleName);
					oldC_ChargeType_ID = line.getC_ChargeType_ID();
					sheet.addCell(label);
					if (rowC_ChargeType != 0) {
						number = new Number(col+1, rowC_ChargeType, C_ChargeType_lineSum_B_AmtAcctdr, C_ChargeTyperowstyle);
						sheet.addCell(number);
						number = new Number(col+2, rowC_ChargeType, C_ChargeType_lineSum_B_AmtAcctcr, C_ChargeTyperowstyle);
						sheet.addCell(number);
						number = new Number(col+3, rowC_ChargeType, C_ChargeType_lineSum_AmtAcctdr, C_ChargeTyperowstyle);
						sheet.addCell(number);
						number = new Number(col+4, rowC_ChargeType, C_ChargeType_lineSum_AmtAcctcr, C_ChargeTyperowstyle);
						sheet.addCell(number);
						number = new Number(col+5, rowC_ChargeType, C_ChargeType_lineSum_E_AmtAcctdr, C_ChargeTyperowstyle);
						sheet.addCell(number);
						number = new Number(col+6, rowC_ChargeType, C_ChargeType_lineSum_E_AmtAcctcr, C_ChargeTyperowstyle);
						sheet.addCell(number);
						
						C_ChargeType_lineSum_B_AmtAcctdr=0;
						C_ChargeType_lineSum_B_AmtAcctcr=0;
						C_ChargeType_lineSum_AmtAcctdr=0;
						C_ChargeType_lineSum_AmtAcctcr=0;
						C_ChargeType_lineSum_E_AmtAcctdr=0;
						C_ChargeType_lineSum_E_AmtAcctcr=0;
					}
					rowC_ChargeType = row; 
				}
				
				rowName = line.getName();
				lineSubconto_ID = line.getSubconto_ID();

				row++;
				label = new Label(col, row, line.getName(), totalnumstyleName);
				sheet.addCell(label);
				if (rowSubconto_ID != 0) {
					number = new Number(col+1, rowSubconto_ID, lineSum_B_AmtAcctdr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+2, rowSubconto_ID, lineSum_B_AmtAcctcr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+3, rowSubconto_ID, lineSum_AmtAcctdr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+4, rowSubconto_ID, lineSum_AmtAcctcr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+5, rowSubconto_ID, lineSum_E_AmtAcctdr, totalnumstyle);
					sheet.addCell(number);
					number = new Number(col+6, rowSubconto_ID, lineSum_E_AmtAcctcr, totalnumstyle);
					sheet.addCell(number);

					lineSum_B_AmtAcctdr=0;
				    lineSum_B_AmtAcctcr=0;
				    lineSum_AmtAcctdr=0;
				    lineSum_AmtAcctcr=0;
				    lineSum_E_AmtAcctdr=0;
				    lineSum_E_AmtAcctcr=0;
				}
				
/*				
				for(int i=col+1; i < 7; i++) {
					label = new Label(i, row, "", partrowstyle);
					sheet.addCell(label);
				}
				
*/				
				rowSubconto_ID = row;
			}
			String val = line.getValue();
			
			if ((WithoutSubaccount.equals("Y") && val.length() == 6) || WithoutSubaccount.equals("N") || val.equals("0ТС")) {
				row++;
				label = new Label(col, row, line.getValue(), valuestyle);
				sheet.addCell(label);
				number = new Number(col+1, row, line.getB_AmtAcctdr(), numericstyle);
				sheet.addCell(number);
				number = new Number(col+2, row, line.getB_AmtAcctcr(), numericstyle);
				sheet.addCell(number);
				number = new Number(col+3, row, line.getAmtAcctdr(), numericstyle);
				sheet.addCell(number);
				number = new Number(col+4, row, line.getAmtAcctcr(), numericstyle);
				sheet.addCell(number);
				number = new Number(col+5, row, line.getE_AmtAcctdr(), numericstyle);
				sheet.addCell(number);
				number = new Number(col+6, row, line.getE_AmtAcctcr(), numericstyle);
				sheet.addCell(number);
			}
			if (line.getValue().length() == 6 || line.getValue().equals("0ТС")) {
			    Sum_B_AmtAcctdr+=line.getB_AmtAcctdr();
			    Sum_B_AmtAcctcr+=line.getB_AmtAcctcr();
			    Sum_AmtAcctdr+=line.getAmtAcctdr();
			    Sum_AmtAcctcr+=line.getAmtAcctcr();
			    Sum_E_AmtAcctdr+=line.getE_AmtAcctdr();
			    Sum_E_AmtAcctcr+=line.getE_AmtAcctcr();
			    
			    lineSum_B_AmtAcctdr+=line.getB_AmtAcctdr();
			    lineSum_B_AmtAcctcr+=line.getB_AmtAcctcr();
			    lineSum_AmtAcctdr+=line.getAmtAcctdr();
			    lineSum_AmtAcctcr+=line.getAmtAcctcr();
			    lineSum_E_AmtAcctdr+=line.getE_AmtAcctdr();
			    lineSum_E_AmtAcctcr+=line.getE_AmtAcctcr();
			    
			    C_ChargeType_lineSum_B_AmtAcctdr+=line.getB_AmtAcctdr();
			    C_ChargeType_lineSum_B_AmtAcctcr+=line.getB_AmtAcctcr();
			    C_ChargeType_lineSum_AmtAcctdr+=line.getAmtAcctdr();
			    C_ChargeType_lineSum_AmtAcctcr+=line.getAmtAcctcr();
			    C_ChargeType_lineSum_E_AmtAcctdr+=line.getE_AmtAcctdr();
			    C_ChargeType_lineSum_E_AmtAcctcr+=line.getE_AmtAcctcr();
			}
		}

//		
/*		
		if (lineSubconto_ID != 0) {
		    row ++;
			label = new Label(col, row, rowName, totalrowstyle);
			sheet.addCell(label);
			number = new Number(col+1, row, lineSum_B_AmtAcctdr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+2, row, lineSum_B_AmtAcctcr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+3, row, lineSum_AmtAcctdr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+4, row, lineSum_AmtAcctcr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+5, row, lineSum_E_AmtAcctdr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+6, row, lineSum_E_AmtAcctcr, totalnumstyle);
			sheet.addCell(number);
		    
			lineSum_B_AmtAcctdr=0;
		    lineSum_B_AmtAcctcr=0;
		    lineSum_AmtAcctdr=0;
		    lineSum_AmtAcctcr=0;
		    lineSum_E_AmtAcctdr=0;
		    lineSum_E_AmtAcctcr=0;
			row++;
			
		}
*/
		if (rowSubconto_ID != 0) {
			number = new Number(col+1, rowSubconto_ID, lineSum_B_AmtAcctdr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+2, rowSubconto_ID, lineSum_B_AmtAcctcr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+3, rowSubconto_ID, lineSum_AmtAcctdr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+4, rowSubconto_ID, lineSum_AmtAcctcr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+5, rowSubconto_ID, lineSum_E_AmtAcctdr, totalnumstyle);
			sheet.addCell(number);
			number = new Number(col+6, rowSubconto_ID, lineSum_E_AmtAcctcr, totalnumstyle);
			sheet.addCell(number);

			lineSum_B_AmtAcctdr=0;
		    lineSum_B_AmtAcctcr=0;
		    lineSum_AmtAcctdr=0;
		    lineSum_AmtAcctcr=0;
		    lineSum_E_AmtAcctdr=0;
		    lineSum_E_AmtAcctcr=0;
			
			rowSubconto_ID = row;
		}
		
		if (rowC_ChargeType != 0) {
			number = new Number(col+1, rowC_ChargeType, C_ChargeType_lineSum_B_AmtAcctdr, C_ChargeTyperowstyle);
			sheet.addCell(number);
			number = new Number(col+2, rowC_ChargeType, C_ChargeType_lineSum_B_AmtAcctcr, C_ChargeTyperowstyle);
			sheet.addCell(number);
			number = new Number(col+3, rowC_ChargeType, C_ChargeType_lineSum_AmtAcctdr, C_ChargeTyperowstyle);
			sheet.addCell(number);
			number = new Number(col+4, rowC_ChargeType, C_ChargeType_lineSum_AmtAcctcr, C_ChargeTyperowstyle);
			sheet.addCell(number);
			number = new Number(col+5, rowC_ChargeType, C_ChargeType_lineSum_E_AmtAcctdr, C_ChargeTyperowstyle);
			sheet.addCell(number);
			number = new Number(col+6, rowC_ChargeType, C_ChargeType_lineSum_E_AmtAcctcr, C_ChargeTyperowstyle);
			sheet.addCell(number);
		}
		
		row++;
		label = new Label(col, row, "Итого:", totalrowstyle1);
		sheet.addCell(label);
		number = new Number(col+1, row, Sum_B_AmtAcctdr, totalrowstyle2);
		sheet.addCell(number);
		number = new Number(col+2, row, Sum_B_AmtAcctcr, totalrowstyle2);
		sheet.addCell(number);
		number = new Number(col+3, row, Sum_AmtAcctdr, totalrowstyle2);
		sheet.addCell(number);
		number = new Number(col+4, row, Sum_AmtAcctcr, totalrowstyle2);
		sheet.addCell(number);
		number = new Number(col+5, row, Sum_E_AmtAcctdr, totalrowstyle2);
		sheet.addCell(number);
		number = new Number(col+6, row, Sum_E_AmtAcctcr, totalrowstyle2);
		sheet.addCell(number);
 
		copy.write();
        copy.close();
        tableBook.close();
        temporaryFile.delete();

		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
		return "OK";
	}
	
	private Boolean isC_Charge( BigDecimal pFI_SubcontoType_ID) {
		Boolean result = false;
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal rFI_SubcontoType_ID = null;
		try {
			String sql = "select FI_SubcontoType_ID from adempiere.FI_SubcontoType where AD_Table_ID in (select AD_Table_ID from adempiere.AD_Table where TableName like 'C_Charge') ";
			pstmt = DB.prepareStatement(sql,null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				rFI_SubcontoType_ID = new BigDecimal(rs.getInt(1));
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		if (rFI_SubcontoType_ID != null) {
			result = pFI_SubcontoType_ID.equals(rFI_SubcontoType_ID);
		}
		return result;
	}
	
	private FI_SubcontoType[] getFI_SubcontoType() {
		List<FI_SubcontoType> result = new ArrayList<FI_SubcontoType>();
		
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
		try {
			pstmt = DB.prepareStatement(getSql(AD_Client_ID, FI_SubcontoType_ID,pBDP,pEDP,Subconto_ID),null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				FI_SubcontoType line = new FI_SubcontoType();
				line.setAD_Client_ID(rs.getInt(colAD_Client_ID));
				line.setAD_Org_ID(rs.getInt(colAD_Org_ID));
				line.setAccount_ID(rs.getInt(colAccount_ID));
				line.setValue(rs.getString(colValue));
				line.setSubconto_ID(rs.getInt(colSubconto_ID));
				line.setName(rs.getString(colName));
				line.setB_AmtAcctdr(rs.getDouble(colB_AmtAcctdr));
				line.setB_AmtAcctcr(rs.getDouble(colB_AmtAcctcr));
				line.setE_AmtAcctdr(rs.getDouble(colE_AmtAcctdr));
				line.setE_AmtAcctcr(rs.getDouble(colE_AmtAcctcr));
				line.setAmtAcctdr(rs.getDouble(colAmtAcctdr));
				line.setAmtAcctcr(rs.getDouble(colAmtAcctcr));
				line.setC_ChargeType_ID(rs.getInt(colC_ChargeType_ID));
				line.setC_ChargeType_Name(rs.getString(colC_ChargeType_Name));
				result.add(line);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		return result.toArray(new FI_SubcontoType[result.size()]);
	}
	
	private String getSubcontoTypeName() {
		String result = "";
		String sql = "select name from FI_SubcontoType where AD_Client_ID = ? and AD_Org_ID = ? and FI_SubcontoType_ID = ?";

		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		try {
			pstmt = DB.prepareStatement(sql,null);
			pstmt.setInt (1, AD_Client_ID.intValue());
			pstmt.setInt (2, AD_Org_ID.intValue());
			pstmt.setInt (3, FI_SubcontoType_ID.intValue());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result = rs.getString(1);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return result;
	}

	private File downloadAttachment(String reportPath) {
		File reportFile = null;
		String name = reportPath.substring("attachment:".length()).trim();
		MProcess process = new MProcess(Env.getCtx(), pi.getAD_Process_ID(), pi.getTransactionName());
		attachment = process.getAttachment();
		if (attachment != null) {
			MAttachmentEntry[] entries = attachment.getEntries();
			MAttachmentEntry entry = null;
			for (int i = 0; i < entries.length; i++) {
				if (entries[i].getName().equals(name)) {
					entry = entries[i];
					break;
				}
			}
			if (entry != null) {
				reportFile = Util.getAttachmentEntryFile(entry);
			}
		}
		return reportFile;
	}
	
	private String getOrgName() {
		String result = null;
		String Sql = "select name from adempiere.ad_org where AD_Org_ID = " + AD_Org_ID.toString();
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
		try {
			pstmt = DB.prepareStatement(Sql,null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result = rs.getString(1);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		return result;
	}
	
	protected String getXlsName(String reportName) throws JRException {
		String Result = null;
		String tmpName = reportName;
		Integer i = 0;
		
		File d = new File(Util.localFilePath);
        String[] lF = d.list(new WildcardFileFilter(tmpName+"*.xls"));
        
        for(int j=0; j < lF.length; j++) {
        	File tmp = new File(lF[j]);
        	tmp.delete();
        }
        
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + tmpName+".xls");
			try { 
				if (f.exists() && !f.delete()) {
					tmpName = reportName + "(" +(++i).toString() + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				tmpName = reportName + "(" +(++i).toString() + ")";
			}
		}
		Result = Util.localFilePath + tmpName+".xls";
		return Result;
	}
	
	public class FI_SubcontoType  {
		
		private int AD_Client_ID;
		private int AD_Org_ID;
		private int Account_ID;
		private String Value;
		private int Subconto_ID;
		private String Name;
		private int C_ChargeType_ID;
		private String C_ChargeType_Name;
		private double B_AmtAcctdr;
		private double B_AmtAcctcr;
		private double E_AmtAcctdr;
		private double E_AmtAcctcr;
		private double AmtAcctdr;
		private double AmtAcctcr;
		
		public int getAD_Client_ID() {
			return AD_Client_ID;
		}

		public void setAD_Client_ID(int AD_Client_ID) {
			this.AD_Client_ID = AD_Client_ID;
		}

		public int getAD_Org_ID() {
			return AD_Org_ID;
		}

		public void setAD_Org_ID(int AD_Org_ID) {
			this.AD_Org_ID = AD_Org_ID;
		}
		
		public int getAccount_ID() {
			return Account_ID;
		}

		public void setAccount_ID(int Account_ID) {
			this.Account_ID = Account_ID;
		}

		public String getValue() {
			return Value;
		}

		public void setValue(String Value) {
			this.Value = Value;
		}

		public int getSubconto_ID() {
			return Subconto_ID;
		}

		public void setSubconto_ID(int Subconto_ID) {
			this.Subconto_ID = Subconto_ID;
		}

		public String getName() {
			return Name;
		}

		public void setName(String Name) {
			this.Name = Name;
		}

		public int getC_ChargeType_ID() {
			return C_ChargeType_ID;
		}

		public void setC_ChargeType_ID(int C_ChargeType_ID) {
			this.C_ChargeType_ID = C_ChargeType_ID;
		}

		public String getC_ChargeType_Name() {
			return C_ChargeType_Name;
		}

		public void setC_ChargeType_Name(String C_ChargeType_Name) {
			this.C_ChargeType_Name = C_ChargeType_Name;
		}

		public double getB_AmtAcctdr() {
			return B_AmtAcctdr;
		}

		public void setB_AmtAcctdr(double B_AmtAcctdr) {
			this.B_AmtAcctdr = B_AmtAcctdr;
		}

		public double getB_AmtAcctcr() {
			return B_AmtAcctcr;
		}

		public void setB_AmtAcctcr(double B_AmtAcctcr) {
			this.B_AmtAcctcr = B_AmtAcctcr;
		}

		public double getE_AmtAcctdr() {
			return E_AmtAcctdr;
		}

		public void setE_AmtAcctdr(double E_AmtAcctdr) {
			this.E_AmtAcctdr = E_AmtAcctdr;
		}

		public double getE_AmtAcctcr() {
			return E_AmtAcctcr;
		}

		public void setE_AmtAcctcr(double E_AmtAcctcr) {
			this.E_AmtAcctcr = E_AmtAcctcr;
		}

		public double getAmtAcctdr() {
			return AmtAcctdr;
		}

		public void setAmtAcctdr(double AmtAcctdr) {
			this.AmtAcctdr = AmtAcctdr;
		}

		public double getAmtAcctcr() {
			return AmtAcctcr;
		}

		public void setAmtAcctcr(double AmtAcctcr) {
			this.AmtAcctcr = AmtAcctcr;
		}

	}

}
